''' <summary>
''' Base class for all classes to read data
''' </summary>
Public MustInherit Class clsData
    Implements IDisposable

    ''' <summary>
    ''' Is raised during long operations (e.g., import/export) so caller can report using UI
    ''' </summary>
    ''' <param name="Value">Current value (must alway initialize with 0 and finalize with Value=MaxValue)</param>
    ''' <param name="MaxValue">Maximum value</param>
    ''' <param name="ProgressText">Text to display</param>
    Public Event Progress(Value As Long, MaxValue As Long, ProgressText As String)

    Protected Sub RaiseProgress(Value As Long, MaxValue As Long, ProgressText As String)
        RaiseEvent Progress(Value, MaxValue, ProgressText)
    End Sub

    Public Enum enumDataType
        BMD
        BMD2
        DSS
        'Excel
        OleDB
        Paradox
        StoretXML
        StoretStaXML
        Text
        WDM
        HBN
        GSSHA
        LspcOut
        LspcAir
        EFDC
        CuashiSta
        YSI
        SQLite
    End Enum

    Protected mFilename As String
    Protected mRecordNum As Integer 'used for error reporting
    Protected mHasHeaders As Boolean
    Protected mFieldNames As Generic.List(Of String)
    Protected mItems As Generic.List(Of String)
    Protected mTableNames As Generic.List(Of String)
    Protected mStationIDs As Generic.List(Of String)
    Protected mPCodes As Generic.List(Of String)
    Protected mActiveTable As String
    Protected mActiveStationID As String
    Protected mActiveStationIDSet As Boolean
    Protected mDataType As enumDataType
    Protected mMinDate, mMaxDate As Date
    Protected mMinValue(), mMaxValue() As Single
    Protected dictFieldIndex As Generic.Dictionary(Of String, Integer) = Nothing
    Protected dictPCodeIndex As Generic.Dictionary(Of String, Integer) = Nothing
    Protected dictIJK_StationID As Generic.Dictionary(Of String, String) = Nothing  'key is parsed I,J value, value is station ID

    Private WRDBFields() As String = {"Station_ID", "PCode", "Date_Time", "Result", "RCode", "CCode", "Agency", "LEW_Pct", "Depth_M"}

    Private WRDBFieldsUpper() As String = {"STATION_ID", "PCODE", "DATE_TIME", "RESULT", "RCODE", "CCODE", "AGENCY", "LEW_PCT", "DEPTH_M"}

    ''' <summary>
    ''' Instantiate the proper class based on the file extension (and contents if .OUT); initialize will have already been called
    ''' </summary>
    Shared Function Instantiate(ByVal FileName As String) As clsData
        Dim Data As clsData = Nothing
        Dim ext As String = IO.Path.GetExtension(FileName).ToUpper
        Select Case ext
            Case ".BMD" : Data = New clsDataBMD : Data.Initialize(FileName)
            Case ".BMD2" : Data = New clsDataBMD2 : Data.Initialize(FileName)
            Case ".DSS" : Data = New clsDataDSS : Data.Initialize(FileName)
            Case ".DB" : Data = New clsDataParadox : Data.Initialize(FileName)
            Case ".MDB", ".DBF", ".XLS", ".XLSX", ".XLSM" : Data = New clsDataOleDB : Data.Initialize(FileName)
            Case ".CSV", ".TXT" : Data = New clsDataText : Data.Initialize(FileName)
            Case ".SDB" : Data = New clsDataSDB : Data.Initialize(FileName)
            Case ".WDM" : Data = New clsDataWDM : Data.Initialize(FileName)
            Case ".HBN" : Data = New clsDataHBN : Data.Initialize(FileName)
            Case ".TS" : Data = New clsDataGSSHA : Data.Initialize(FileName)
            Case ".AIR" : Data = New clsDataLspcAir : Data.Initialize(FileName)
            Case ".DAT" : Data = New clsDataYSI : Data.Initialize(FileName)
            Case ".OUT"
                Dim sr As New IO.StreamReader(FileName)
                Dim s As String = sr.ReadLine
                sr.Close()
                If s.StartsWith("TT") Then
                    Data = New WRDB.DataSource.clsDataLspcOut : Data.Initialize(FileName)
                Else
                    Application.UseWaitCursor = False
                    s = InputBox("Enter year associated with the beginning of the simulation from which julian days are derived:", "EFDC Base Year", Now.Year)
                    If Not IsNumeric(s) Then
                        Return Nothing
                    Else
                        Data = New WRDB.DataSource.clsDataEFDC : Data.Initialize(FileName)
                        CType(Data, WRDB.DataSource.clsDataEFDC).CalendarYear = s
                    End If
                End If
        End Select
        Return Data
    End Function

    ''' <summary>
    ''' Initialize class
    ''' </summary>
    ''' <param name="_Filename">Name of file to read</param>
    Public Overridable Sub Initialize(ByVal _Filename As String)
        mFilename = _Filename
        mRecordNum = -1
        mFieldNames = New Generic.List(Of String)
        mItems = New Generic.List(Of String)
        mTableNames = New Generic.List(Of String)
        mStationIDs = New Generic.List(Of String)
        mPCodes = New Generic.List(Of String)
        mMinDate = Date.MinValue
        mMaxDate = Date.MaxValue
    End Sub

    ''' <summary>
    ''' Read next line from file into memory, storing into mItems array
    ''' Also save mLastLineRead for error reporting
    ''' </summary>
    ''' <returns>True if successful, false if eof reached and no more data or user cancelled</returns>
    Public Overridable Function Read() As Boolean
        mRecordNum += 1
        Return True
    End Function

    ''' <summary>
    ''' Returns number or records read so far
    ''' </summary>
    Public ReadOnly Property NumRead() As Integer
        Get
            Return mRecordNum + 1
        End Get
    End Property

    ''' <summary>
    ''' Returns last number of items read
    ''' </summary>
    Public ReadOnly Property NumItems() As Integer
        Get
            Return mItems.Count
        End Get
    End Property

    ''' <summary>
    ''' Resets datasource so new read operation starts at beginning of file
    ''' For array-based data sources, no override needed; others need to close then reopen
    ''' </summary>
    Public Overridable Sub Rewind()
        mRecordNum = -1
    End Sub

    ''' <summary>
    ''' Resets datasource so new read operation starts at beginning of file
    ''' For array-based data sources, no override needed; others need to close then reopen
    ''' </summary>
    Public Overridable Sub Rewind(ByVal _HasHeaders As Boolean)
        mHasHeaders = _HasHeaders
        Rewind()
    End Sub

    ''' <summary>
    ''' Return the file name of database or table
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property FileName() As String
        Get
            Return mFilename
        End Get
    End Property

    ''' <summary>
    ''' For data sources with multiple tables (or tabs or data subsets), return the list of table names
    ''' </summary>
    ''' <returns></returns>
    Public Overridable Function TableNames() As Generic.List(Of String)
        Return mTableNames
    End Function

    ''' <summary>
    ''' Return the list of unique station ids
    ''' </summary>
    ''' <returns></returns>
    Public Overridable Function StationIDs() As Generic.List(Of String)
        Return mStationIDs
    End Function

    ''' <summary>
    ''' Return the station id given an IJK value
    ''' </summary>
    ''' <param name="IJK">In format I,J,K</param>
    ''' <param name="StationID">Station ID</param>
    ''' <returns>True if found, false otherwise</returns>
    Public Function TryGetStationIDFromIJK(IJK As String, ByRef StationID As String) As Boolean
        If dictIJK_StationID.ContainsKey(IJK) Then
            StationID = dictIJK_StationID(IJK)
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Return the list of unique pcodes
    ''' </summary>
    ''' <returns></returns>
    Public Overridable Function PCodes() As Generic.List(Of String)
        Return mPCodes
    End Function

    ''' <summary>
    ''' Returns list of column headers or field names
    ''' </summary>
    Public ReadOnly Property FieldNames() As Generic.List(Of String)
        Get
            Return mFieldNames
        End Get
    End Property

    ''' <summary>
    ''' Given a fieldname, return the parsed value representing the PCode (used primarily of LSPC/EFCD import)
    ''' </summary>
    ''' <param name="FieldName">Field name</param>
    Public Overridable Function PCodeFromField(ByVal FieldName As String) As String
        Return Strings.Left(FieldName, 10).Trim
    End Function

    ''' <summary>
    ''' See if field name is found (case insensitive)
    ''' </summary>
    ''' <param name="FieldName">Name of field to check</param>
    ''' <returns>True if found</returns>
    Public Function ContainsFieldName(ByVal FieldName As String) As Boolean
        Return FieldIndex(FieldName) <> -1
    End Function

    ''' <summary>
    ''' Determine index of named field or PCode (case insensitive)
    ''' </summary>
    ''' <param name="FieldName">Name of field to check</param>
    ''' <returns>Index if found, -1 if not found</returns>
    Public Overridable Function FieldIndex(ByVal FieldName As String) As Integer
        If dictFieldIndex Is Nothing Then 'must be initialized to speed later searches
            dictFieldIndex = New Generic.Dictionary(Of String, Integer)
            For i As Integer = 0 To mFieldNames.Count - 1
                If dictFieldIndex.ContainsKey(mFieldNames(i).ToUpper) Then
                    Throw New DataException("Duplicate field names were encountered in the import file; please make sure all columns are unique: " & mFieldNames(i))
                Else
                    dictFieldIndex.Add(mFieldNames(i).ToUpper, i)
                End If
            Next
            dictPCodeIndex = New Generic.Dictionary(Of String, Integer)
            For i As Integer = 0 To mFieldNames.Count - 1
                Dim PCode As String = PCodeFromField(mFieldNames(i)).ToUpper
                If Not dictPCodeIndex.ContainsKey(PCode) Then
                    dictPCodeIndex.Add(PCode, i)
                End If
            Next
        End If

        Dim index As Integer = -1
        If dictFieldIndex.TryGetValue(FieldName.ToUpper, index) Then Return index
        If dictPCodeIndex.TryGetValue(FieldName.ToUpper, index) Then Return index
        Return -1
    End Function

    ''' <summary>
    ''' Retrieve specified item on last line read
    ''' </summary>
    ''' <returns>Contents of the field for last record read</returns>
    Public ReadOnly Property Items(ByVal Index As Integer) As String
        Get
            If Index < 0 Or Index > mItems.Count - 1 Then Return "" Else Return mItems(Index)
        End Get
    End Property

    ''' <summary>
    ''' Retrieve specified item on last line read
    ''' </summary>
    ''' <returns>Contents of the field for last record read</returns>
    Public ReadOnly Property Items(ByVal FieldName As String) As String
        Get
            Dim i As Integer = FieldIndex(FieldName)
            If i = -1 Then
                Throw New ArgumentException("Field name " & FieldName & " not found in dataset.")
            Else
                Return mItems(i)
            End If
        End Get
    End Property

    ''' <summary>
    ''' If parsing I,J,K from station name, return list of unique K values (will be empty list if none found)
    ''' </summary>
    Public Overridable ReadOnly Property Layers As List(Of Integer)
        Get
            Dim lst As New List(Of Integer)
            For Each sta As String In mStationIDs
                Dim ar() As String = StringSplit(sta, "K=", True)
                If ar.Length = 2 Then
                    Dim k As String = Val(ar(1))
                    If Not lst.Contains(k) Then lst.Add(k)
                End If
            Next
            lst.Sort()
            Return lst
        End Get
    End Property

    ''' <summary>
    ''' Currently active table or data subset
    ''' </summary>
    Public Overridable Property ActiveTable() As String
        Get
            Return mActiveTable
        End Get
        Set(ByVal value As String)
            mRecordNum = -1
            dictFieldIndex = Nothing
            dictPCodeIndex = Nothing
            mActiveTable = value
        End Set
    End Property

    ''' <summary>
    ''' Currently active station ID (may allow some sources to avoid long sequential reads)
    ''' </summary>
    Public Overridable Property ActiveStationID() As String
        Get
            Return mActiveStationID
        End Get
        Set(ByVal value As String)
            Rewind()
            mActiveStationIDSet = True
            mActiveStationID = value
        End Set
    End Property

    ''' <summary>
    ''' Return data table containing station IDs and other properties that will be used by frmFilter
    ''' For most data types will just return list of station IDs, but for BMD2 will override to return additional metadata
    ''' </summary>
    ''' <returns>Datatable containing unique station IDs</returns>
    ''' <remarks></remarks>
    Public Overridable Function GetStationTable() As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("Station ID")
        For Each s As String In mStationIDs
            dt.Rows.Add(s)
        Next
        Return dt
    End Function

    ''' <summary>
    ''' Return data table containing PCodes and other properties that will be used by frmFilter
    ''' For more data types will just return list of PCodes, but for BMD2 will override to return additional metadata
    ''' </summary>
    ''' <returns>Datatable containing unique PCodes</returns>
    ''' <remarks></remarks>
    Public Overridable Function GetPCodeTable() As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("PCode")
        For Each s As String In mPCodes
            dt.Rows.Add(s)
        Next
        Return dt
    End Function

    Protected Enum enumDataTableColumns
        All
        Station_Result
        Station_Date_Result
        Date_Result
    End Enum

    ''' <summary>
    ''' Get data table containing time series data for all stations and pcodes
    ''' Table will contain all source data fields
    ''' </summary>
    Protected Function SetupDataTable(DataTableColumns As enumDataTableColumns) As DataTable
        Dim dt As New DataTable
        With dt.Columns
            Select Case DataTableColumns
                Case enumDataTableColumns.All
                    For Each s As String In FieldNames
                        .Add(s, GetType(String))
                    Next
                Case enumDataTableColumns.Station_Result
                    .Add("Station_ID", GetType(String))
                    .Add("Result", GetType(Single))
                Case enumDataTableColumns.Station_Date_Result
                    .Add("Station_ID", GetType(String))
                    .Add("Date_Time", GetType(Date))
                    .Add("Result", GetType(Single))
                Case enumDataTableColumns.Date_Result
                    .Add("Date_Time", GetType(Date))
                    .Add("Result", GetType(Single))
            End Select
        End With
        Return dt
    End Function

    Public Enum enumAggregateFunction
        Num
        Min
        Max
        Mean
        Median
        Geomean
        Pct5
        Pct10
        Pct25
        Pct75
        Pct90
        Pct95
    End Enum

    ''' <summary>
    ''' Controls how layer attributes are linked to data files
    ''' </summary>
    Public Enum enumLinkageType
        ''' <summary>Require exact match between one attribute field and segment name or metadata</summary>
        StationID

        ''' <summary>Link attribute RECID with segment index</summary>
        SegmentNum

        ''' <summary>Parse segment name to get I,J and link to I and J attribute fields</summary>
        ParseIJK

        ''' <summary>Get I and J from metadata and link to I and J attribute fields</summary>
        IJtoIJ
    End Enum

    ''' <summary>
    ''' Get aggregate value for all data in given time range for station id and pcode
    ''' If requested station ID or PCode is not found, will throw exception and return double.minvalue
    ''' This routine is called by frmGIS and is used for animation: all values at a given time or range are assembled for rapid display
    ''' Has been overridden for BMD, and can also override for other types to make them faster (TO-DO item)
    ''' </summary>
    ''' <param name="StationIDList">List of desired Station IDs as discerned from attribute table (Station ID, RECID, or I,J value)</param>
    ''' <param name="LinkageType">Linkage type; if IJ then above list contains I,J values and matches are found by parsing the StationID in the datasource</param>
    ''' <param name="PCode">Desired PCode</param>
    ''' <param name="MinDate">Minimum date to include (inclusive)</param>
    ''' <param name="MaxDate">Maximum date to include (non-inclusive)</param>
    ''' <param name="AggregateFunction">How the raw data must be aggregated</param>
    Public Overridable Function GetResults(StationIDList As Generic.List(Of String), LinkageType As enumLinkageType, PCode As String, MinDate As Date, MaxDate As Date, AggregateFunction As enumAggregateFunction) As Generic.Dictionary(Of String, Single)

        'if parseIJ then create dictionary linking station IDs with IJ values
        If LinkageType = enumLinkageType.ParseIJK AndAlso dictIJK_StationID Is Nothing Then
            dictIJK_StationID = New Dictionary(Of String, String)
            For Each staID As String In mStationIDs
                Dim IJK As String = ""
                If TryParseIJK(staID, IJK) AndAlso Not dictIJK_StationID.ContainsKey(IJK) Then dictIJK_StationID.Add(IJK, staID)
            Next
        End If

        Const RunInParallel As Boolean = False

        Static LastPCode As String = ""

        'use thread-safe concurrent dictionary for parallel processing
        Static dictTables As Concurrent.ConcurrentDictionary(Of String, DataTable) = Nothing

        If PCode <> LastPCode Then

            'need to acquire data into datatable for fast extraction; data table will contain Date_Time, Station_ID, and Values
            'if later call with different Station_ID, Dates, or Aggregation, will just filter & compute the table; if different PCode will need to reacquire

            'use parallel processing to fill the table faster

            'use thread-safe concurrent dictionary for parallel processing
            dictTables = New Concurrent.ConcurrentDictionary(Of String, DataTable)

            Dim t1 As Date = Now
            Dim ds As WRDB.DataSource.clsData = WRDB.DataSource.clsData.Instantiate(mFilename)

            If RunInParallel Then
                'note: last index is non-inclusive!
                Threading.Tasks.Parallel.For(0, StationIDList.Count, Sub(i)
                                                                         Dim staID As String = StationIDList(i)
                                                                         If LinkageType = enumLinkageType.ParseIJK Then
                                                                             'staID contains IJ pair
                                                                             Dim stationID As String = ""
                                                                             If dictIJK_StationID.TryGetValue(staID, stationID) Then
                                                                                 Dim dt As DataTable = ds.GetDataTable(stationID, PCode) 'each table has Date_Time and Result columns
                                                                                 dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
                                                                                 dictTables(staID) = dt
                                                                             End If
                                                                         Else
                                                                             If mStationIDs.Contains(staID) Then
                                                                                 Dim dt As DataTable = ds.GetDataTable(staID, PCode) 'each table has Date_Time and Result columns
                                                                                 dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
                                                                                 dictTables(staID) = dt
                                                                             End If
                                                                         End If
                                                                     End Sub)
            Else
                For i As Integer = 0 To StationIDList.Count - 1
                    Dim staID As String = StationIDList(i)
                    If LinkageType = enumLinkageType.ParseIJK Then
                        'staID contains IJ pair
                        Dim stationID As String = ""
                        If dictIJK_StationID.TryGetValue(staID, stationID) Then
                            Dim dt As DataTable = ds.GetDataTable(stationID, PCode) 'each table has Date_Time and Result columns
                            dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
                            dictTables(staID) = dt
                        End If
                    Else
                        If mStationIDs.Contains(staID) Then
                            Dim dt As DataTable = ds.GetDataTable(staID, PCode) 'each table has Date_Time and Result columns
                            dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
                            dictTables(staID) = dt
                        End If
                    End If
                    RaiseProgress(i, mStationIDs.Count - 1, "Compiling data: " & staID)
                Next i
            End If

            LastPCode = PCode
        End If

        Dim dictResults As New Generic.Dictionary(Of String, Single)

        If RunInParallel Then
            Dim dictResultsConcurrent As New Concurrent.ConcurrentDictionary(Of String, Single)

            Threading.Tasks.Parallel.ForEach(StationIDList, Sub(StationID As String)
                                                                'I've assembled time series for this pcode for all stations, return aggregate value within date range for this particular station
                                                                If Not dictTables.ContainsKey(StationID) Then
                                                                    dictResultsConcurrent(StationID) = -1
                                                                Else
                                                                    Dim dtData As DataTable = dictTables(StationID)

                                                                    Dim value As Double = -1

                                                                    If MinDate = MaxDate Then
                                                                        Select Case AggregateFunction
                                                                            Case enumAggregateFunction.Num
                                                                                dictResultsConcurrent(StationID) = 1
                                                                            Case Else
                                                                                dictResultsConcurrent(StationID) = TestNull(dtData.Rows.Find(MinDate)(1), -1)
                                                                        End Select
                                                                    Else
                                                                        Dim aggFunc As String = ""
                                                                        Select Case AggregateFunction
                                                                            Case enumAggregateFunction.Num : aggFunc = "Count"
                                                                            Case enumAggregateFunction.Min : aggFunc = "Min"
                                                                            Case enumAggregateFunction.Max : aggFunc = "Max"
                                                                            Case enumAggregateFunction.Mean : aggFunc = "Avg"
                                                                        End Select
                                                                        Dim rowfilter As String = String.Format("Date_Time>=#{0}# AND Date_Time<#{1}#", MinDate, MaxDate)
                                                                        Dim agg As Double = -1

                                                                        If aggFunc <> "" Then 'use simple aggregation

                                                                            agg = TestNull(dtData.Compute(String.Format("{0}(Result)", aggFunc), rowfilter), -1)

                                                                        Else 'is advanced aggregate statistic; need to iterate through all rows

                                                                            Dim lstDta As New List(Of Double)
                                                                            For Each dta As DataRow In dtData.Select(rowfilter)
                                                                                If Not IsDBNull(dta("Result")) Then lstDta.Add(dta("Result"))
                                                                            Next
                                                                            Dim arPct() As Double = Nothing
                                                                            modProbability.CalcStats(lstDta.ToArray, arPct)

                                                                            Select Case AggregateFunction
                                                                                Case enumAggregateFunction.Geomean
                                                                                    Dim geomean As Double = 0.0
                                                                                    Dim num As Integer = 0
                                                                                    For Each d As Double In lstDta
                                                                                        If d > 0.0 Then
                                                                                            num += 1
                                                                                            geomean += Math.Log(d)
                                                                                        End If
                                                                                    Next
                                                                                    If num > 0 Then agg = Math.Exp(geomean / num)
                                                                                Case enumAggregateFunction.Median : agg = arPct(9)
                                                                                Case enumAggregateFunction.Pct5 : agg = arPct(0)
                                                                                Case enumAggregateFunction.Pct10 : agg = arPct(1)
                                                                                Case enumAggregateFunction.Pct25 : agg = arPct(4)
                                                                                Case enumAggregateFunction.Pct75 : agg = arPct(14)
                                                                                Case enumAggregateFunction.Pct90 : agg = arPct(17)
                                                                                Case enumAggregateFunction.Pct95 : agg = arPct(18)
                                                                            End Select
                                                                        End If

                                                                        dictResultsConcurrent(StationID) = agg

                                                                    End If
                                                                End If
                                                            End Sub)

            'copy back to normal dictionary
            For Each key As String In dictResultsConcurrent.Keys
                dictResults.Add(key, dictResultsConcurrent(key))
            Next
        Else
            For Each StationID As String In StationIDList
                'I've assembled time series for this pcode for all stations, return aggregate value within date range for this particular station
                If Not dictTables.ContainsKey(StationID) Then
                    dictResults.Add(StationID, -1)
                Else
                    Dim dtData As DataTable = dictTables(StationID)

                    Dim value As Double = -1

                    If MinDate = MaxDate Then
                        Select Case AggregateFunction
                            Case enumAggregateFunction.Num
                                dictResults.Add(StationID, 1)
                            Case Else
                                dictResults.Add(StationID, TestNull(dtData.Rows.Find(MinDate)(1), -1))
                        End Select
                    Else
                        Dim aggFunc As String = ""
                        Select Case AggregateFunction
                            Case enumAggregateFunction.Num : aggFunc = "Count"
                            Case enumAggregateFunction.Min : aggFunc = "Min"
                            Case enumAggregateFunction.Max : aggFunc = "Max"
                            Case enumAggregateFunction.Mean : aggFunc = "Avg"
                        End Select
                        Dim rowfilter As String = String.Format("Date_Time>=#{0}# AND Date_Time<#{1}#", MinDate, MaxDate)
                        Dim agg As Double = -1

                        If aggFunc <> "" Then 'use simple aggregation

                            agg = TestNull(dtData.Compute(String.Format("{0}(Result)", aggFunc), rowfilter), -1)

                        Else 'is advanced aggregate statistic; need to iterate through all rows

                            Dim lstDta As New List(Of Double)
                            For Each dta As DataRow In dtData.Select(rowfilter)
                                If Not IsDBNull(dta("Result")) Then lstDta.Add(dta("Result"))
                            Next
                            Dim pct() As Double = Nothing
                            modProbability.CalcStats(lstDta.ToArray, pct)

                            Select Case AggregateFunction
                                Case enumAggregateFunction.Geomean
                                    Dim geomean As Double = 0.0
                                    Dim num As Integer = 0
                                    For Each d As Double In lstDta
                                        If d > 0.0 Then
                                            num += 1
                                            geomean += Math.Log(d)
                                        End If
                                    Next
                                    If num > 0 Then agg = Math.Exp(geomean / num)
                                Case enumAggregateFunction.Median : agg = pct(9)
                                Case enumAggregateFunction.Pct5 : agg = pct(0)
                                Case enumAggregateFunction.Pct10 : agg = pct(1)
                                Case enumAggregateFunction.Pct25 : agg = pct(4)
                                Case enumAggregateFunction.Pct75 : agg = pct(14)
                                Case enumAggregateFunction.Pct90 : agg = pct(17)
                                Case enumAggregateFunction.Pct95 : agg = pct(18)
                            End Select
                        End If

                        dictResults(StationID) = agg

                    End If
                End If
            Next
        End If

        Return dictResults

    End Function

    Protected Function TryParseIJK(StationID As String, ByRef IJK As String) As Boolean
        'try to extract I,J,K from Station ID
        If StationID.Contains("I=") And StationID.Contains("J=") Then
            Dim ar() As String = StationID.Split(New Char() {"="})
            IJK = String.Format("{0},{1}", Val(ar(1)), Val(ar(2)))
            If StationID.Contains("K=") Then
                IJK &= "," & Val(ar(3))
            End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Get aggregate value for all data in given time range for station id and pcode
    ''' If requested station ID or PCode is not found, will throw exception and return double.minvalue
    ''' This routine is called by frmGIS and is used for animation: all values at a given time or range are assembled for rapid display
    ''' Has been overridden for BMD, and can also override for other types to make them faster (TO-DO item)
    ''' This version will return magnitude and direction tuple for display of vector data
    ''' </summary>
    ''' <param name="StationIDList">List of desired Station ID</param>
    ''' <param name="PCodeX">Desired PCode for x component</param>
    ''' <param name="PCodeY">Desired PCode for y component</param>
    ''' <param name="MinDate">Minimum date to include (inclusive)</param>
    ''' <param name="MaxDate">Maximum date to include (non-inclusive)</param>
    ''' <param name="AggregateFunction">How the raw data must be aggregated (note: only some functions are supported; caller will really only need means for velocity vectors)</param>
    Public Overridable Function GetResults(StationIDList As Generic.List(Of String),
                                           PCodeX As String, PCodeY As String,
                                           MinDate As Date, MaxDate As Date,
                                           AggregateFunction As enumAggregateFunction) As Generic.Dictionary(Of String, Tuple(Of Single, Single))
        Static LastPCodeX As String = ""
        Static LastPCodeY As String = ""

        'use thread-safe concurrent dictionary for parallel processing
        Static dictTables As Concurrent.ConcurrentDictionary(Of String, DataTable) = Nothing

        If PCodeX <> LastPCodeX Or PCodeY <> LastPCodeY Then

            'need to acquire data into datatable for fast extraction; data table will contain Date_Time, Station_ID, and Values
            'if later call with different Station_ID, Dates, or Aggregation, will just filter & compute the table; if different PCode will need to reacquire

            'use parallel processing to fill the table faster

            'use thread-safe concurrent dictionary for parallel processing
            dictTables = New Concurrent.ConcurrentDictionary(Of String, DataTable)

            Dim t1 As Date = Now

            'note: last index is non-inclusive!
            Threading.Tasks.Parallel.For(0, StationIDList.Count, Sub(i)
                                                                     Dim staID As String = StationIDList(i)
                                                                     If mStationIDs.Contains(staID) Then
                                                                         Dim ds As WRDB.DataSource.clsData = WRDB.DataSource.clsData.Instantiate(mFilename)
                                                                         Dim dtX As DataTable = ds.GetDataTable(staID, PCodeX) 'has Date_Time and Result columns
                                                                         Dim dtY As DataTable = ds.GetDataTable(staID, PCodeY) 'has Date_Time and Result columns
                                                                         dtX.PrimaryKey = New DataColumn() {dtX.Columns(0)}
                                                                         'add another column and merge the two tables (this could be slow)
                                                                         dtX.Columns.Add("Direction", GetType(Single))
                                                                         For Each dr As DataRow In dtY.Rows
                                                                             Dim drX As DataRow = dtX.Rows.Find(dr(0))
                                                                             If drX IsNot Nothing Then drX(2) = dr(1)
                                                                         Next
                                                                         dictTables(staID) = dtX
                                                                     End If
                                                                 End Sub)
            LastPCodeX = PCodeX
            LastPCodeY = PCodeY
        End If

        Dim dictResults As New Generic.Dictionary(Of String, Tuple(Of Single, Single))

        Dim dictResultsConcurrent As New Concurrent.ConcurrentDictionary(Of String, Tuple(Of Single, Single))

        Threading.Tasks.Parallel.ForEach(StationIDList, Sub(StationID As String)
                                                            'I've assembled time series for this pcode for all stations, return aggregate value within date range for this particular station
                                                            If Not dictTables.ContainsKey(StationID) Then
                                                                dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(-1.0, -1.0)
                                                            Else
                                                                Dim dtData As DataTable = dictTables(StationID)

                                                                Dim value As Double = -1

                                                                If MinDate = MaxDate Then 'no aggregation required, just return magnitudes
                                                                    Select Case AggregateFunction
                                                                        Case enumAggregateFunction.Num
                                                                            dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(1, 1)
                                                                        Case Else
                                                                            Dim dr As DataRow = dtData.Rows.Find(MinDate)
                                                                            If dr Is Nothing Then
                                                                                dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(-1.0, -1.0)
                                                                            Else
                                                                                If dr Is Nothing OrElse (IsDBNull(dr(1)) Or IsDBNull(dr(2))) Then
                                                                                    dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(-1.0, -1.0)
                                                                                Else
                                                                                    Dim x As Single = dr(1)
                                                                                    Dim y As Single = dr(2)
                                                                                    Dim l As Single = Math.Sqrt(x * x + y * y)
                                                                                    Dim a As Single = Math.Atan2(y, x)
                                                                                    dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(l, a)
                                                                                End If
                                                                            End If
                                                                    End Select
                                                                Else
                                                                    Dim rowfilter As String = String.Format("Date_Time>=#{0}# AND Date_Time<#{1}#", MinDate, MaxDate)
                                                                    'note: cannot use Compute function to get results because want to know the angle associated with 
                                                                    'the max and min values; instead just calculate vector for each value
                                                                    Dim cnt As Integer = 0, sum As Single = 0, sumx As Single = 0, sumy As Single = 0,
                                                                        min As Single = Single.MaxValue, max As Single = Single.MinValue, angle As Single = 0
                                                                    Dim l As Single = 0, a As Single = 0
                                                                    For Each dr As DataRow In dtData.Select(rowfilter)
                                                                        If dr IsNot Nothing AndAlso Not IsDBNull(dr(1)) AndAlso Not IsDBNull(dr(2)) Then
                                                                            Dim x As Single = dr(1)
                                                                            Dim y As Single = dr(2)
                                                                            l = Math.Sqrt(x * x + y * y)
                                                                            a = Math.Atan2(y, x)
                                                                            cnt += 1
                                                                            sumx += x
                                                                            sumy += y
                                                                            sum += l
                                                                            Select Case AggregateFunction
                                                                                Case enumAggregateFunction.Min
                                                                                    If l < min Then
                                                                                        min = l
                                                                                        angle = a
                                                                                    End If
                                                                                Case enumAggregateFunction.Max
                                                                                    If l > max Then
                                                                                        max = l
                                                                                        angle = a
                                                                                    End If
                                                                            End Select
                                                                        End If
                                                                    Next
                                                                    Select Case AggregateFunction
                                                                        Case enumAggregateFunction.Num : l = cnt
                                                                        Case enumAggregateFunction.Min : l = min : a = angle
                                                                        Case enumAggregateFunction.Max : l = max : a = angle
                                                                        Case enumAggregateFunction.Mean
                                                                            If cnt > 0 Then
                                                                                l = sum / cnt
                                                                                a = Math.Atan2(sumy, sumx)
                                                                            End If
                                                                    End Select
                                                                    dictResultsConcurrent(StationID) = New Tuple(Of Single, Single)(l, a)
                                                                End If
                                                            End If
                                                        End Sub)

        'copy back to normal dictionary
        For Each key As String In dictResultsConcurrent.Keys
            dictResults.Add(key, dictResultsConcurrent(key))
        Next

        Return dictResults

    End Function

    ''' <summary>
    ''' Get data table containing all data over then entire time range for specific station id and pcode
    ''' Table will contain date field and one field for each station id; routine should only be used for model output files like BMD and LSPC
    ''' If requested station ID or PCode is not found, will throw exception and return Nothing
    ''' </summary>
    Public Overridable Function GetDataTable(StationID As String, PCode As String) As DataTable
        Dim itemNum As Integer = -1
        For i As Integer = 0 To mFieldNames.Count - 1
            If mFieldNames(i).StartsWith(PCode) Then
                itemNum = i
                Exit For
            End If
        Next
        If itemNum = -1 Then
            Throw New ApplicationException("File does not contain the column: " & PCode)
            Return Nothing
        End If
        ActiveStationID = StationID
        Dim dt As DataTable = SetupDataTable(enumDataTableColumns.Date_Result)
        While Read()
            dt.Rows.Add(Items("Date_Time"), Items(itemNum))
        End While
        Return dt
    End Function

    ''' <summary>
    ''' Get data table containing time series data for all stations and pcodes
    ''' Table will contain date-time field and result field
    ''' </summary>
    Public Overridable Function GetDataTable() As DataTable
        Dim dt As New DataTable
        With dt.Columns
            For Each s As String In FieldNames
                dt.Columns.Add(s, GetType(String))
            Next
        End With
        Return dt
    End Function

    ''' <summary>
    ''' Get data table containing time series data for a specified station id 
    ''' Table will contain all fields
    ''' </summary>
    Public Function GetDataTable(ByVal StationID As String) As DataTable
        Dim dt As DataTable = SetupDataTable(enumDataTableColumns.All)
        ActiveStationID = StationID
        While Read()
            Dim row As DataRow = dt.NewRow
            For i As Integer = 0 To NumItems - 1
                row(i) = Items(i)
            Next
            dt.Rows.Add(row)
        End While
        Return dt
    End Function

    ''' <summary>
    ''' Get data table containing all data in given time range for all station ids and pcode
    ''' Table will contain station and result field
    ''' </summary>
    ''' <remarks>
    ''' Called by GIS for for animation; this routine does brute-force loading; suggest override as follows:
    ''' <list>
    ''' BMD: direct access to dates and times
    ''' LSPC: no more efficient means to do this (WRDB 5.1 completely changes how animation is done)
    ''' SQL: perform SQL query
    ''' </list>
    ''' </remarks>
    Public Overridable Function GetDataTable(ByVal StationIDs() As String, ByVal PCode As String, MinDate As Date, MaxDate As Date) As DataTable
        Dim dt As DataTable = SetupDataTable(enumDataTableColumns.Station_Result)
        For Each StaID As String In StationIDs
            ActiveStationID = StaID
            While Read()
                Dim DateTime As Date = Items("Date_Time")
                If DateTime >= MinDate And DateTime <= MaxDate Then dt.Rows.Add(StaID, Items(PCode))
            End While
        Next
        Return dt
    End Function

    Public Overridable ReadOnly Property MinDate() As Date
        Get
            If mMinDate = Date.MinValue Then GetMinMaxDates()
            Return mMinDate
        End Get
    End Property

    Public Overridable ReadOnly Property MaxDate() As Date
        Get
            If mMaxDate = Date.MaxValue Then GetMinMaxDates()
            Return mMaxDate
        End Get
    End Property

    Public Overridable ReadOnly Property MinValue(PCode As String) As Single
        Get
            If mMinValue Is Nothing Then GetMinMaxValues()
            Return mMinValue(mPCodes.IndexOf(PCode))
        End Get
    End Property

    Public Overridable ReadOnly Property MaxValue(PCode As String) As Single
        Get
            If mMaxValue Is Nothing Then GetMinMaxValues()
            Return mMaxValue(mPCodes.IndexOf(PCode))
        End Get
    End Property

    ''' <summary>
    ''' Determine min/max values for each pcodes regardless of station id; generic version uses brute force by looping over all entries
    ''' </summary>
    ''' <remarks></remarks>
    Protected Overridable Sub GetMinMaxValues()
        ReDim mMinValue(mPCodes.Count - 1)
        ReDim mMaxValue(mPCodes.Count - 1)
        For v As Integer = 0 To mPCodes.Count - 1
            mMinValue(v) = Double.MaxValue
            mMaxValue(v) = Double.MinValue
        Next
        Rewind()
        While Read()
            For v As Integer = 0 To mPCodes.Count - 1
                Dim value As Double = Items(mPCodes(v))
                mMinValue(v) = Math.Min(mMinValue(v), value)
                mMaxValue(v) = Math.Max(mMaxValue(v), value)
            Next
        End While
        Rewind()
    End Sub

    ''' <summary>
    ''' Determine min/max dates; generic version uses brute force by looping over all entries
    ''' </summary>
    Protected Overridable Sub GetMinMaxDates()
        mMinDate = Date.MaxValue
        mMaxDate = Date.MinValue
        Dim datefld As Integer = FieldIndex("Date_Time")
        If datefld = -1 Then Exit Sub
        Rewind()
        While Read()
            Dim dt As Date = mItems(datefld)
            If dt < mMinDate Then mMinDate = dt
            If dt > mMaxDate Then mMaxDate = dt
        End While
        Rewind()
    End Sub

    ''' <summary>
    ''' Retrieve all dates in datasource; generic version uses brute force by looping over all entries (but BMD will override)
    ''' </summary>
    Public Overridable Function Dates() As List(Of DateTime)
        Debug.Assert(False)
        Dim lst As New List(Of DateTime)
        Dim datefld As Integer = FieldIndex("Date_Time")
        If datefld <> -1 Then
            Rewind()
            While Read()
                lst.Add(CDate(mItems(datefld)))
            End While
            Rewind()
        End If
        Return lst
    End Function

    ''' <summary>
    ''' Convenient method of determining data file type
    ''' </summary>
    Public ReadOnly Property DataType() As enumDataType
        Get
            Return mDataType
        End Get
    End Property

    ''' <summary>
    ''' Determine percent of file that has been read
    ''' </summary>
    ''' <returns>Percent of file read</returns>
    Public MustOverride Function PercentComplete() As Double

    Protected Function RemoveBrackets(ByVal s As String) As String
        Return s.Replace("[", "").Replace("]", "").Replace("'", "").Replace("$", "")
    End Function

    ''' <summary>
    ''' Close the data source; override this to close any open files
    ''' </summary>
    Public Overridable Sub Close()
        mFieldNames.Clear()
        mItems.Clear()
        mTableNames.Clear()
        mStationIDs.Clear()
        mPCodes.Clear()
    End Sub

    Public Overloads Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

    Private disposed As Boolean = False

    Protected Overridable Overloads Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposed Then
            If disposing Then
                'put disposal items here
            End If
        End If
        Me.disposed = True
    End Sub

    Protected Overrides Sub Finalize()
        Dispose(False)
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' Round date-time value to the nearest minute
    ''' </summary>
    ''' <param name="Original">Value to be rounded</param>
    ''' <returns></returns>
    ''' <remarks>This is necessary because slight round-off errors when truncated will appear as, e.g., 11:59 rather than 12:00</remarks>
    Shared Function RoundDateTime(ByVal Original As Date) As Date
        With Original
            If .Second >= 30 Then Original = .AddMinutes(1)
            Return New Date(.Year, .Month, .Day, .Hour, .Minute, 0)
        End With
    End Function

    Public Function IsWRDBField(ByVal FieldName As String) As Boolean
        Return Array.IndexOf(WRDBFieldsUpper, FieldName.ToUpper) <> -1
        'For Each s As String In WRDBFields
        '    If FieldName.Equals(s, StringComparison.OrdinalIgnoreCase) Then Return True
        'Next
        'Return False
    End Function

End Class

